CREATE PROCEDURE [dbo].[sp_asi_MeetingsFlowdown]
(
@ID VARCHAR(10),
@OLD_COMPANY VARCHAR(80),
@NEW_COMPANY VARCHAR(80),
@COMPANY_SORT VARCHAR(90),
@OLD_CO_ID VARCHAR(10),
@NEW_CO_ID VARCHAR(10),
@OLD_EMAIL VARCHAR(100),
@NEW_EMAIL VARCHAR(100),
@OLD_PHONE VARCHAR(25),
@NEW_PHONE VARCHAR(25),
@OLD_FAX VARCHAR(25),
@NEW_FAX VARCHAR(25),
@OLD_TOLL_FREE VARCHAR(25),
@NEW_TOLL_FREE VARCHAR(25),
@PREF_MAIL_CHANGED VARCHAR(2),
@ADDRESS_1 VARCHAR(40),
@ADDRESS_2 VARCHAR(40),
@ADDRESS_3 VARCHAR(40),
@CITY VARCHAR(40),
@STATE_PROVINCE VARCHAR(15),
@ZIP VARCHAR(10),
@COUNTRY VARCHAR(25),
@BAR_CODE VARCHAR(14),
@DPB VARCHAR(8),
@OLD_PREF_MAIL_ADDR VARCHAR(255),
@NEW_PREF_MAIL_ADDR VARCHAR(255),
@OLD_FULL_NAME VARCHAR(60),
@NEW_FULL_NAME VARCHAR(60),
@BADGE_FULL_NAME VARCHAR(60),
@PREFIX VARCHAR(10),
@FIRST_NAME VARCHAR(20),
@MIDDLE_NAME VARCHAR(20),
@LAST_NAME VARCHAR(30),
@SUFFIX VARCHAR(10),
@DESIGNATION VARCHAR(20),
@LAST_FIRST VARCHAR(30),
@OLD_TITLE VARCHAR(80),
@NEW_TITLE VARCHAR(80),
@OLD_INFORMAL VARCHAR(20),
@NEW_INFORMAL VARCHAR(20),
@MAINADDR_IS_PREFMAIL VARCHAR(2),
@MAIN_ADDR_CHANGED VARCHAR(2),
@MAIN_ADDRESS_1 VARCHAR(40),
@MAIN_ADDRESS_2 VARCHAR(40),
@MAIN_ADDRESS_3 VARCHAR(40),
@MAIN_CITY VARCHAR(40),
@MAIN_STATE_PROVINCE VARCHAR(15),
@MAIN_ZIP VARCHAR(10),
@MAIN_COUNTRY VARCHAR(25),
@MAIN_BAR_CODE VARCHAR(14),
@MAIN_DPB VARCHAR(8),
@OLD_MAIN_ADDR VARCHAR(255),
@NEW_MAIN_ADDR VARCHAR(255),
@OLD_PREF_MAIL_ADDR_1 VARCHAR(255),
@OLD_PREF_MAIL_ADDR_2 VARCHAR(40),
@OLD_PREF_MAIL_ADDR_3 VARCHAR(40),
@OLD_PREF_MAIL_CITY VARCHAR(40),
@OLD_PREF_MAIL_STATE VARCHAR(15),
@OLD_PREF_MAIL_ZIP VARCHAR(10),
@OLD_PREF_MAIL_COUNTRY VARCHAR(25),
@OLD_MAIN_ADDR_1 VARCHAR(255),
@OLD_MAIN_ADDR_2 VARCHAR(40),
@OLD_MAIN_ADDR_3 VARCHAR(40),
@OLD_MAIN_CITY VARCHAR(40),
@OLD_MAIN_STATE VARCHAR(15),
@OLD_MAIN_ZIP VARCHAR(10),
@OLD_MAIN_COUNTRY VARCHAR(25)
)
AS
declare
@CompanyPurposes varchar(255),
@TitlePurposes varchar(255),
@PrefPurpose varchar(255),
@UseCompany bit,
@UseTitle bit
select @CompanyPurposes =ShortValue from System_Params where ParameterName = 'Member_Control.PrintCompanyWithAddressString'
select @TitlePurposes =ShortValue from System_Params where ParameterName = 'Member_Control.PrintTitleWithAddressString'
select @UseCompany=0
select @UseTitle=0
SELECT Orders.*, Name.COMPANY_RECORD, Name.ADDRESS_NUM_1, Name.MAIL_ADDRESS_NUM, 'N' as NAME_UPDATED,
'N' as TITLE_UPDATED, 'N' as INFORMAL_UPDATED, 'N' as COMPANY_UPDATED, 'N' as ADDRESS_UPDATED,
'N' as BLOCK_ADDRESS_UPDATE, 'N' as BLOCK_PREF_MAIL_UPDATE
INTO #tmp_meet_flowdown
FROM Orders INNER JOIN Name
ON Name.ID = Orders.ST_ID
WHERE
Orders.SOURCE_SYSTEM in ('MEETING','EXPO','EXHIBITION') AND
(Orders.ST_ID = @ID OR
Orders.CO_ID = @ID)
IF (select ShortValue from System_Params where ParameterName = 'Member_Control.DisableAutoFlowDown') = 'YES'
BEGIN
IF @OLD_COMPANY != @NEW_COMPANY
BEGIN
UPDATE #tmp_meet_flowdown set COMPANY_UPDATED = 'Y'
WHERE COMPANY = @OLD_COMPANY
UPDATE Orders SET Orders.COMPANY = @NEW_COMPANY,
Orders.COMPANY_SORT = @COMPANY_SORT
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.COMPANY_UPDATED = 'Y'
UPDATE Order_Badge SET Order_Badge.COMPANY = @NEW_COMPANY
FROM #tmp_meet_flowdown, Order_Badge
WHERE Order_Badge.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.COMPANY_UPDATED = 'Y' AND
(Order_Badge.BADGE_TYPE = 'PRIMARY' OR Order_Badge.COMPANY = @OLD_COMPANY)
END
BEGIN
DELETE from #tmp_meet_flowdown where CO_ID = @ID
END
END
DELETE from #tmp_meet_flowdown where COMPANY_RECORD = 1 and CO_ID = @ID
UPDATE #tmp_meet_flowdown SET BLOCK_ADDRESS_UPDATE = 'Y'
WHERE CO_ID = @ID and ADDRESS_NUM_1 != MAIL_ADDRESS_NUM
IF @MAINADDR_IS_PREFMAIL != 'Y'
BEGIN
UPDATE #tmp_meet_flowdown SET BLOCK_PREF_MAIL_UPDATE = 'Y'
WHERE CO_ID = @ID
END
IF @OLD_CO_ID != @NEW_CO_ID
BEGIN
UPDATE Orders SET Orders.CO_ID = @NEW_CO_ID
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
Orders.ST_ID = @ID AND
Orders.CO_ID = @OLD_CO_ID
END
IF @OLD_EMAIL != @NEW_EMAIL
BEGIN
UPDATE Orders SET Orders.EMAIL = @NEW_EMAIL
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
Orders.ST_ID = @ID AND
Orders.EMAIL = @OLD_EMAIL
END
IF @OLD_FULL_NAME != @NEW_FULL_NAME
BEGIN
IF @OLD_FULL_NAME = ' '
BEGIN
UPDATE #tmp_meet_flowdown set NAME_UPDATED = 'Y'
WHERE ST_ID = @ID AND FULL_NAME = ' '
END
ELSE
BEGIN
UPDATE #tmp_meet_flowdown set NAME_UPDATED = 'Y'
WHERE ST_ID = @ID AND FULL_NAME LIKE '%' + @OLD_FULL_NAME + '%'
END
UPDATE Orders SET
Orders.FULL_NAME = @NEW_FULL_NAME,
Orders.PREFIX = @PREFIX,
Orders.FIRST_NAME = @FIRST_NAME,
Orders.MIDDLE_NAME = @MIDDLE_NAME,
Orders.LAST_NAME = @LAST_NAME,
Orders.SUFFIX = @SUFFIX,
Orders.DESIGNATION = @DESIGNATION,
Orders.LAST_FIRST = @LAST_FIRST
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.NAME_UPDATED = 'Y'
UPDATE Order_Badge SET
Order_Badge.FULL_NAME = @BADGE_FULL_NAME,
Order_Badge.PREFIX = @PREFIX,
Order_Badge.FIRST_NAME = @FIRST_NAME,
Order_Badge.MIDDLE_NAME = @MIDDLE_NAME,
Order_Badge.LAST_NAME = @LAST_NAME,
Order_Badge.SUFFIX = @SUFFIX,
Order_Badge.DESIGNATION = @DESIGNATION
FROM #tmp_meet_flowdown, Order_Badge
WHERE Order_Badge.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.NAME_UPDATED = 'Y' AND
Order_Badge.BADGE_TYPE = 'PRIMARY'
END
IF @OLD_TITLE != @NEW_TITLE
BEGIN
IF @OLD_FULL_NAME = ' '
BEGIN
UPDATE #tmp_meet_flowdown set TITLE_UPDATED = 'Y'
WHERE ST_ID = @ID AND FULL_NAME = ' '
AND TITLE = @OLD_TITLE
END
ELSE
BEGIN
UPDATE #tmp_meet_flowdown set TITLE_UPDATED = 'Y'
WHERE ST_ID = @ID AND FULL_NAME LIKE '%' + @OLD_FULL_NAME + '%'
AND TITLE = @OLD_TITLE
END
UPDATE Orders SET Orders.TITLE = @NEW_TITLE
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.TITLE_UPDATED = 'Y'
UPDATE Order_Badge SET Order_Badge.TITLE = @NEW_TITLE
FROM #tmp_meet_flowdown, Order_Badge
WHERE Order_Badge.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.TITLE_UPDATED = 'Y' AND
Order_Badge.BADGE_TYPE = 'PRIMARY'
END
IF @OLD_INFORMAL != @NEW_INFORMAL
BEGIN
UPDATE #tmp_meet_flowdown set INFORMAL_UPDATED = 'Y'
WHERE ST_ID = @ID AND INFORMAL = @OLD_INFORMAL
UPDATE Orders SET Orders.INFORMAL = @NEW_INFORMAL
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.INFORMAL_UPDATED = 'Y'
UPDATE Order_Badge SET Order_Badge.INFORMAL = @NEW_INFORMAL
FROM #tmp_meet_flowdown, Order_Badge
WHERE Order_Badge.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.INFORMAL_UPDATED = 'Y' AND
Order_Badge.BADGE_TYPE = 'PRIMARY'
END
IF @OLD_PHONE != @NEW_PHONE
BEGIN
UPDATE Orders SET Orders.PHONE = @NEW_PHONE
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
Orders.PHONE = @OLD_PHONE
END
IF @OLD_FAX != @NEW_FAX
BEGIN
UPDATE Orders SET Orders.FAX = @NEW_FAX
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
Orders.FAX = @OLD_FAX
END
IF @OLD_TOLL_FREE != @NEW_TOLL_FREE
BEGIN
UPDATE Orders SET Orders.TOLL_FREE = @NEW_TOLL_FREE
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
Orders.TOLL_FREE = @OLD_TOLL_FREE
END
IF @OLD_COMPANY != @NEW_COMPANY
BEGIN
UPDATE #tmp_meet_flowdown set COMPANY_UPDATED = 'Y'
WHERE COMPANY = @OLD_COMPANY
UPDATE Orders SET Orders.COMPANY = @NEW_COMPANY,
Orders.COMPANY_SORT = @COMPANY_SORT
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.COMPANY_UPDATED = 'Y'
UPDATE Order_Badge SET Order_Badge.COMPANY = @NEW_COMPANY
FROM #tmp_meet_flowdown, Order_Badge
WHERE Order_Badge.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.COMPANY_UPDATED = 'Y' AND
(Order_Badge.BADGE_TYPE = 'PRIMARY' OR Order_Badge.COMPANY = @OLD_COMPANY)
END
IF @PREF_MAIL_CHANGED = 'Y'
BEGIN
select @PrefPurpose=','+PURPOSE+',' from Name_Address
join Name on Name.ID=Name_Address.ID
where Name.MAIL_ADDRESS_NUM=Name_Address.ADDRESS_NUM and Name.ID=@ID
If charindex(@PrefPurpose,@CompanyPurposes)>0
begin
select @UseCompany=1
end
If charindex(@PrefPurpose,@TitlePurposes)>0
begin
select @UseTitle=1
end
UPDATE #tmp_meet_flowdown set ADDRESS_UPDATED = 'Y'
WHERE ADDRESS_1 = @OLD_PREF_MAIL_ADDR_1 AND
ADDRESS_2 = @OLD_PREF_MAIL_ADDR_2 AND
ADDRESS_3 = @OLD_PREF_MAIL_ADDR_3 AND
CITY = @OLD_PREF_MAIL_CITY AND
STATE_PROVINCE = @OLD_PREF_MAIL_STATE AND
ZIP = @OLD_PREF_MAIL_ZIP AND
COUNTRY = @OLD_PREF_MAIL_COUNTRY
UPDATE Orders SET
Orders.ADDRESS_1 = @ADDRESS_1,
Orders.ADDRESS_2 = @ADDRESS_2,
Orders.ADDRESS_3 = @ADDRESS_3,
Orders.CITY = @CITY,
Orders.STATE_PROVINCE = @STATE_PROVINCE,
Orders.ZIP = @ZIP,
Orders.COUNTRY = @COUNTRY,
Orders.DPB = @DPB,
Orders.BAR_CODE = @BAR_CODE,
Orders.FULL_ADDRESS = @NEW_PREF_MAIL_ADDR,
Orders.ST_PRINT_COMPANY = @UseCompany,
Orders.ST_PRINT_TITLE = @UseTitle,
Orders.ST_ADDRESS_NUM=#tmp_meet_flowdown.MAIL_ADDRESS_NUM
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.ADDRESS_UPDATED = 'Y' AND
#tmp_meet_flowdown.BLOCK_ADDRESS_UPDATE = 'N' AND
#tmp_meet_flowdown.BLOCK_PREF_MAIL_UPDATE = 'N'
UPDATE Order_Badge SET
Order_Badge.ADDRESS_1 = @ADDRESS_1,
Order_Badge.ADDRESS_2 = @ADDRESS_2,
Order_Badge.ADDRESS_3 = @ADDRESS_3,
Order_Badge.CITY = @CITY,
Order_Badge.STATE_PROVINCE = @STATE_PROVINCE,
Order_Badge.ZIP = @ZIP,
Order_Badge.COUNTRY = @COUNTRY,
Order_Badge.BAR_CODE = @BAR_CODE,
Order_Badge.FULL_ADDRESS = @NEW_PREF_MAIL_ADDR,
Order_Badge.ST_PRINT_COMPANY = case Order_Badge.BADGE_TYPE when 'PRIMARY' then @UseCompany else Order_Badge.ST_PRINT_COMPANY end,
Order_Badge.ST_PRINT_TITLE = case Order_Badge.BADGE_TYPE when 'PRIMARY' then @UseTitle else Order_Badge.ST_PRINT_TITLE end
FROM #tmp_meet_flowdown, Order_Badge
WHERE Order_Badge.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.ADDRESS_UPDATED = 'Y' AND
#tmp_meet_flowdown.BLOCK_ADDRESS_UPDATE = 'N' AND
#tmp_meet_flowdown.BLOCK_PREF_MAIL_UPDATE = 'N' AND
(Order_Badge.BADGE_TYPE = 'PRIMARY' OR Order_Badge.FULL_ADDRESS LIKE @OLD_PREF_MAIL_ADDR)
END
IF @MAINADDR_IS_PREFMAIL != 'Y' AND @MAIN_ADDR_CHANGED = 'Y'
BEGIN
UPDATE #tmp_meet_flowdown set ADDRESS_UPDATED = 'Y'
WHERE ADDRESS_1 = @OLD_MAIN_ADDR_1 AND
ADDRESS_2 = @OLD_MAIN_ADDR_2 AND
ADDRESS_3 = @OLD_MAIN_ADDR_3 AND
CITY = @OLD_MAIN_CITY AND
STATE_PROVINCE = @OLD_MAIN_STATE AND
ZIP = @OLD_MAIN_ZIP AND
COUNTRY = @OLD_MAIN_COUNTRY
UPDATE Orders SET
Orders.ADDRESS_1 = @MAIN_ADDRESS_1,
Orders.ADDRESS_2 = @MAIN_ADDRESS_2,
Orders.ADDRESS_3 = @MAIN_ADDRESS_3,
Orders.CITY = @MAIN_CITY,
Orders.STATE_PROVINCE = @MAIN_STATE_PROVINCE,
Orders.ZIP = @MAIN_ZIP,
Orders.COUNTRY = @MAIN_COUNTRY,
Orders.DPB = @MAIN_DPB,
Orders.BAR_CODE = @MAIN_BAR_CODE,
Orders.FULL_ADDRESS = @NEW_MAIN_ADDR
FROM #tmp_meet_flowdown, Orders
WHERE Orders.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.CO_ID = @ID AND
#tmp_meet_flowdown.ADDRESS_UPDATED = 'Y' AND
#tmp_meet_flowdown.BLOCK_ADDRESS_UPDATE = 'N'
UPDATE Order_Badge SET
Order_Badge.ADDRESS_1 = @MAIN_ADDRESS_1,
Order_Badge.ADDRESS_2 = @MAIN_ADDRESS_2,
Order_Badge.ADDRESS_3 = @MAIN_ADDRESS_3,
Order_Badge.CITY = @MAIN_CITY,
Order_Badge.STATE_PROVINCE = @MAIN_STATE_PROVINCE,
Order_Badge.ZIP = @MAIN_ZIP,
Order_Badge.COUNTRY = @MAIN_COUNTRY,
Order_Badge.BAR_CODE = @MAIN_BAR_CODE,
Order_Badge.FULL_ADDRESS = @NEW_MAIN_ADDR
FROM #tmp_meet_flowdown, Order_Badge
WHERE Order_Badge.ORDER_NUMBER = #tmp_meet_flowdown.ORDER_NUMBER AND
#tmp_meet_flowdown.CO_ID = @ID AND
#tmp_meet_flowdown.ADDRESS_UPDATED = 'Y' AND
#tmp_meet_flowdown.BLOCK_ADDRESS_UPDATE = 'N' AND
(Order_Badge.BADGE_TYPE = 'PRIMARY' OR Order_Badge.FULL_ADDRESS LIKE @OLD_MAIN_ADDR)
END
drop table #tmp_meet_flowdown
GO
GRANT EXECUTE ON [dbo].[sp_asi_MeetingsFlowdown] TO [IMIS]
GO
[dbo].[sp_asi_MeetingsFlowdown]